using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using HealthSmart;
using System.Data;
using System.Data.SqlClient;
using NYProviders.Models;

namespace $PROJECT_NAMESPACE$.DataRules
{
	/// <summary>
	/// Data access layer class for $CLASS_NAME$
	/// </summary>
	class $CLASS_NAME$DR
	{

        Connections _dbConn = new Connections();

        #region Constructor

		/// <summary>
		/// Class constructor
		/// </summary>
		public $CLASS_NAME$DR()
		{
			// Nothing for now.
		}

        #endregion

        #region Public Methods

        /// <summary>
        /// insert new row in the table
        /// </summary>
		/// <param name="businessObject">business object</param>
		/// <returns>true of successfully insert</returns>
		public SQLQueryStatus Insert($CLASS_NAME$ businessObject)
		{
			string sp = "$TABLE_SCHEMA$.[$SP_PREFIX$$TABLE_NAME$_Insert]";
                
                SQLQueryStatus status = new SQLQueryStatus();
			try
			{
                status = _dbConn.ExecuteNonQuery(sp, new SqlParameter[]{
    $INSERT_PARAMETER$
});


				
                //$GET_RETURNED_VALUE$
			}
			catch(Exception ex)
			{				
                    status.Status = SQLQueryStatus.QueryStatus.Failure;
				status.Message = "$CLASS_NAME$::Insert::Error occured.";
                    status.Error = ex.Message;
			}

            return status
		}

         /// <summary>
        /// update row in the table
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <returns>true for successfully updated</returns>
        public SQLQueryStatus Update($CLASS_NAME$ businessObject)
        {
            string sp = "$TABLE_SCHEMA$.[$SP_PREFIX$$TABLE_NAME$_Update]";
                
                SQLQueryStatus status = new SQLQueryStatus();
			try
			{
                status = _dbConn.ExecuteNonQuery(sp, new SqlParameter[]{
                 $UPDATE_PARAMETER$
                });


				
                //$GET_RETURNED_VALUE$
			}
			catch(Exception ex)
			{				
                    status.Status = SQLQueryStatus.QueryStatus.Failure;
				status.Message = "$CLASS_NAME$::Insert::Error occured.";
                    status.Error = ex.Message;
			}

            return status
        }

        /// <summary>
        /// Select by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>$CLASS_NAME$ business object</returns>
        public DataSet SelectByPrimaryKey($CLASS_NAME$Keys keys)
        {

            DataSet ds = null;
            
            string sp = "$TABLE_SCHEMA$.[$SP_PREFIX$$TABLE_NAME$_SelectByPrimaryKey]";

            try
            {
                ds = _dbConn.ExecuteQueryDS(sp, new SqlParameter[]{
$SELECT_BY_PK_PARAMETERS$
});

                
            }
            catch (Exception ex)
            {
                //throw new Exception("$CLASS_NAME$::SelectByPrimaryKey::Error occured.", ex);
            }
            
            return ds;
        }

        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>list of $CLASS_NAME$</returns>
        public DataSet SelectAll()
        {

            DataSet ds = null;
            
            string sp = "$TABLE_SCHEMA$.[$SP_PREFIX$$TABLE_NAME$_SelectAll]";

            try
            {
                ds = _dbConn.ExecuteQueryDS(sp, null);

                
            }
            catch (Exception ex)
            {
                //throw new Exception("$CLASS_NAME$::SelectByPrimaryKey::Error occured.", ex);
            }
            
            return ds;
        }

        /// <summary>
        /// Select records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>list of $CLASS_NAME$</returns>
        public DataSet SelectByField(string fieldName, object value)
        {

            DataSet ds = null;
            
            string sp = "$TABLE_SCHEMA$.[$SP_PREFIX$$TABLE_NAME$_SelectByField]";

            try
            {
                ds = _dbConn.ExecuteQueryDS(sp, new SqlParameter[]{new SqlParameter("@FieldName", fieldName),new SqlParameter("@Value", value)});               
            }
            catch (Exception ex)
            {
                //throw new Exception("$CLASS_NAME$::SelectByPrimaryKey::Error occured.", ex);
            }
            
            return ds;

        }

        /// <summary>
        /// Delete by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>true for successfully deleted</returns>
        public SQLQueryStatus Delete($CLASS_NAME$Keys keys)
        {

            SQLQueryStatus ds = new SQLQueryStatus();
            
            string sp = "$TABLE_SCHEMA$.[$SP_PREFIX$$TABLE_NAME$_DeleteByPrimaryKey]";

            try
            {
                ds = _dbConn.ExecuteNonQuery(sp, new SqlParameter[]{$SELECT_BY_PK_PARAMETERS$});

                
            }
            catch (Exception ex)
            {
               ds.Status = SQLQueryStatus.QueryStatus.Failure;
				ds.Message = "$CLASS_NAME$::Delete::Error occured.";
                    ds.Error = ex.Message;
            }
            
            return ds;

        }

        /// <summary>
        /// Delete records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>true for successfully deleted</returns>
        public SQLQueryStatus DeleteByField(string fieldName, object value)
        {

            SQLQueryStatus ds = new SQLQueryStatus();
            
            string sp = "$TABLE_SCHEMA$.[$SP_PREFIX$$TABLE_NAME$_DeleteByField]";

            try
            {
                ds = _dbConn.ExecuteNonQuery(sp, new SqlParameter[]{new SqlParameter("@FieldName", fieldName),new SqlParameter("@Value", value)});               

                
            }
            catch (Exception ex)
            {
               ds.Status = SQLQueryStatus.QueryStatus.Failure;
				ds.Message = "$CLASS_NAME$::Delete::Error occured.";
                    ds.Error = ex.Message;
            }
            
            return ds;

           
        }

        #endregion

	}
}
